/**
 * 
 */
package net.toocruel.iqismart.dao.core;

import net.toocruel.iqismart.dao.base.BaseRepository;
import net.toocruel.iqismart.entity.core.Question;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author toocruel
 *
 */
@Repository
public interface QuestionRepository extends BaseRepository<Question> {

    //select distinct a.userId,t.name,(select count(distinct a.id) from question q,question_tag qt,answer a where qt.tagId in (select qt.tagId from question_tag qt where qt.questionId =7 ) and qt.questionId = q.id and a.questionId = q.id) as count from question q,answer a,question_tag qt,tag t where t.id  in  (select qt.tagId from question_tag qt where qt.questionId =7 ) and qt.`tagId`=t.id and qt.`questionId`=q.`id` and a.`questionId` = q.id;
    @Query(value = "select " +
            "distinct u.id as id,u.username as username," +
            "t.name as tagName," +
            "(select count(distinct a.id) from question q,question_tag qt,answer a where qt.tagId in (select qt.tagId from question_tag qt where qt.questionId =7 ) and qt.questionId = q.id and a.questionId = q.id) as tagAnswers " +
            "from user u, question q,answer a,question_tag qt,tag t " +
            "where t.id  in  (select qt.tagId from question_tag qt where qt.questionId =?1 ) and qt.`tagId`=t.id and qt.`questionId`=q.`id` and a.`questionId` = q.id and u.id = a.userId",nativeQuery = true)
    List<Object[]> getRecommentInviteUsers(Long questionId);

}
